package com.pb.infra.oth.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.streaming.*;
import org.apache.poi.xssf.usermodel.*;

import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.ParseException;
import java.util.*;

/**
 *   @Author:liaohaoyuan
 *   @Date: 2021/5/10
 * 该类能提升导出效率
 * 注意每个sheet最多能导出1058576条数据超出最大条数会报异常
 */
public class ExportData {
    private XSSFCellStyle head_Style;
    private SXSSFWorkbook workbook;
    private SXSSFSheet sheet;
    private SXSSFRow row = null;// 创建一行
    private SXSSFCell cell = null;
    private int currentRow = 0;
    private XSSFCellStyle Integer_style ;
    private XSSFCellStyle string_style;
    private XSSFCellStyle double_style;
    private XSSFCellStyle double2_style;
    private String[][] keyList =null;
    private List<Integer> rownumList = new ArrayList();

    /**
     * 构造函数初始化参数
     *
     * @param headers 表头名
     * @param sheeatName 工作薄名
     * @param comments 批注
     */

    public ExportData(String[][] headers,String[] sheeatName,String[][] comments){
        try{
            workbook=new SXSSFWorkbook();
            this.head_Style=(XSSFCellStyle) this.workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            font.setFontName(Constant.EXCEL_FONT);
            font.setFontHeightInPoints(Constant.EXCEL_DATA_FONT_SIZE);
            font.setColor(IndexedColors.WHITE.getIndex());
            this.head_Style.setFont(font);// 单元格样式使用字体
            this.head_Style.setVerticalAlignment(VerticalAlignment.CENTER);
            this.head_Style.setAlignment(HorizontalAlignment.CENTER);
            this.head_Style.setFillForegroundColor(new XSSFColor(new java.awt.Color(Constant.EXCEL_HEAD_COLOR_R, Constant.EXCEL_HEAD_COLOR_G, Constant.EXCEL_HEAD_COLOR_B)));
            this.head_Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            this.head_Style.setBorderLeft(BorderStyle.THIN);
            this.head_Style.setBorderRight(BorderStyle.THIN);
            this.head_Style.setBorderTop(BorderStyle.THIN);
            this.head_Style.setBorderBottom(BorderStyle.THIN);
            XSSFFont fontStyle = (XSSFFont) workbook.createFont();
            fontStyle.setFontName("宋体");// 设置头部字体为宋体
            fontStyle.setFontHeightInPoints((short) 10);
            this.string_style = (XSSFCellStyle) this.workbook.createCellStyle();
            this.string_style.setAlignment(HorizontalAlignment.LEFT);
            this.string_style.setFont(fontStyle);// 字符单元格样式使用字体
            //数字样式
            this.Integer_style=(XSSFCellStyle) this.workbook.createCellStyle();
            Integer_style.setFont(fontStyle);
            Integer_style.setAlignment(HorizontalAlignment.RIGHT);
            this.double_style=(XSSFCellStyle) this.workbook.createCellStyle();
            double_style.setFont(fontStyle);
            double_style.setDataFormat(workbook.createDataFormat().getFormat(Constant.EXCEL_NUMBER_FORMAT));

            this.double2_style=(XSSFCellStyle) this.workbook.createCellStyle();
            this.double2_style.setFont(fontStyle);
            this.double2_style.setAlignment(HorizontalAlignment.RIGHT);
            this.double2_style.setVerticalAlignment(VerticalAlignment.CENTER);
            this.double2_style.setDataFormat(workbook.createDataFormat().getFormat(Constant.EXCEL_NUMBER_FORMAT2));

            for (int i = 0; i < sheeatName.length; i++) {
                createSheet( sheeatName[i],headers[i],comments!=null?comments[i]:null);
            }
        }catch(Exception exc) {
            exc.printStackTrace();
        }
    }

    /**
     * 构造函数初始化参数
     *
     * @param headers 表头名
     * @param condition 条件名称
     * @param sheeatName 工作薄名
     * @param comments 批注
     */
    public ExportData(String[][][] headers,Map<String,Object> condition, String[] sheeatName,String[][][]  comments,String [][] order){
        try{
            keyList=order;
            workbook=new SXSSFWorkbook();
            XSSFFont fontStyle = (XSSFFont) workbook.createFont();
            fontStyle.setFontName("宋体");// 设置头部字体为宋体
            fontStyle.setFontHeightInPoints((short) 10);
            this.head_Style=(XSSFCellStyle) this.workbook.createCellStyle();
            Font font = workbook.createFont();
            font.setBold(true);
            font.setFontName(Constant.EXCEL_FONT);
            font.setFontHeightInPoints(Constant.EXCEL_DATA_FONT_SIZE);
            font.setColor(IndexedColors.WHITE.getIndex());
            this.head_Style.setFont(font);// 单元格样式使用字体
            this.head_Style.setVerticalAlignment(VerticalAlignment.CENTER);
            this.head_Style.setAlignment(HorizontalAlignment.CENTER);
            this.head_Style.setFillForegroundColor(new XSSFColor(new java.awt.Color(Constant.EXCEL_HEAD_COLOR_R, Constant.EXCEL_HEAD_COLOR_G, Constant.EXCEL_HEAD_COLOR_B)));
            this.head_Style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            this.head_Style.setBorderLeft(BorderStyle.THIN);
            this.head_Style.setBorderRight(BorderStyle.THIN);
            this.head_Style.setBorderTop(BorderStyle.THIN);
            this.head_Style.setBorderBottom(BorderStyle.THIN);
            this.string_style = (XSSFCellStyle) this.workbook.createCellStyle();
            this.string_style.setAlignment(HorizontalAlignment.LEFT);
            this.string_style.setFont(fontStyle);// 字符单元格样式使用字体
            this.string_style.setVerticalAlignment(VerticalAlignment.CENTER);
            //数字样式
            this.Integer_style=(XSSFCellStyle) this.workbook.createCellStyle();
            this.Integer_style.setFont(fontStyle);
            this.Integer_style.setAlignment(HorizontalAlignment.RIGHT);
            this.Integer_style.setVerticalAlignment(VerticalAlignment.CENTER);
            this.double_style=(XSSFCellStyle) this.workbook.createCellStyle();
            this.double_style.setFont(fontStyle);
            this.double_style.setAlignment(HorizontalAlignment.RIGHT);
            this.double_style.setVerticalAlignment(VerticalAlignment.CENTER);
            this.double_style.setDataFormat(workbook.createDataFormat().getFormat(Constant.EXCEL_NUMBER_FORMAT));

            this.double2_style=(XSSFCellStyle) this.workbook.createCellStyle();
            this.double2_style.setFont(fontStyle);
            this.double2_style.setAlignment(HorizontalAlignment.RIGHT);
            this.double2_style.setVerticalAlignment(VerticalAlignment.CENTER);
            this.double2_style.setDataFormat(workbook.createDataFormat().getFormat(Constant.EXCEL_NUMBER_FORMAT2));

            for (int i = 0; i < sheeatName.length; i++) {
                if(i==0){
                    createSheet( sheeatName[i],condition,headers[i],comments.length!=0?comments[i]:null,i);
                }else{
                    createSheet( sheeatName[i],null,headers[i],comments.length!=0?comments[i]:null,i);
                }
            }
        }catch(Exception exc) {
            exc.printStackTrace();
        }
    }

    /**
     * 创建表头
     * @param sheetName  工作薄名
     * @param headers 列名
     * @param comments 批注
     */
    private  void createSheet(String sheetName,Map<String,Object> condition,String[][] headers,String[][] comments,int numflag)  {
        sheet = (SXSSFSheet) workbook.createSheet(sheetName);
        if(condition==null||condition.size()==0){
            numflag=0;
        }else{
            if (numflag==0){
                numflag=3;
            }else{
                numflag=0;
            }
            Set<String> strings = condition.keySet();
            Iterator<String> iterator = strings.iterator();
            int index = 0;
            row = (SXSSFRow) sheet.createRow(1);
            while (iterator.hasNext()){
                String next = iterator.next();
                Object o = condition.get(next);
                String Str = "";
                if (o!=null){
                    o.toString();
                }
                cell = (SXSSFCell) row.createCell(index);
                String test= next+":  "+ condition.get(next);
                cell.setCellValue(test);
                int firstRow = 1;//需要合并的第一个单元格的行数
                int lastRow = 1;//需要合并的最后一个单元格的行数
                int firstCol = index;//需要合并的第一个单元格的列数
                int lastCol = index+1;//需要合并的最后一个单元格的列数
                if (firstRow < lastRow || firstCol < lastCol) {
                    CellRangeAddress cellAddresses = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
                    sheet.addMergedRegion(cellAddresses);
                    // 使用RegionUtil类为合并后的单元格添加边框
                    RegionUtil.setBorderBottom(BorderStyle.THIN, cellAddresses, sheet); // 下边框
                    RegionUtil.setBorderLeft(BorderStyle.THIN, cellAddresses, sheet); // 左边框
                    RegionUtil.setBorderRight(BorderStyle.THIN, cellAddresses, sheet); // 有边框
                    RegionUtil.setBorderTop(BorderStyle.THIN, cellAddresses, sheet); // 上边框
                }
                setColumnWidth(sheet, cell);
                index+=3;
            }
        }
        int size = headers.length+numflag;
        rownumList.add(size);
        List<Map<String,Integer>> list =new ArrayList<>();
        for (int i = numflag; i < size; i++) {
            row = (SXSSFRow) sheet.createRow(i);
            String[] rowcommentsList = comments[i-numflag];
            String[] rowDataList = headers[i-numflag];
            for (int i1 = 0; i1 < rowDataList.length; i1++) {
                boolean flag = false;
                for (Map<String, Integer> strMap : list) {
                    Integer firstRow = strMap.get("firstRow");//需要合并的第一个单元格的行数
                    Integer lastRow = strMap.get("lastRow");//需要合并的最后一个单元格的行数
                    Integer firstCol = strMap.get("firstCol");//需要合并的第一个单元格的列数
                    Integer lastCol = strMap.get("lastCol");//需要合并的最后一个单元格的列数
                    if((i>=firstRow&&i<=lastRow)&&(i1>=firstCol&&i1<=lastCol)){
                        flag = true;
                        break;
                    }
                }
                if(flag){
                    continue;
                }
                String rowData = rowDataList[i1];
                int rownum = 1;
                for (int i2 = i1+1; i2 < rowDataList.length; i2++) {
                    String rowData2 = rowDataList[i2];
                    /*if(i!=0){
                        int index = i-1;
                        List<Object> objects = headersList.get(index);//上一行的数据
                        String colStr = objects.get(i2).toString();//上一行同一列的数据
                        if (colStr.equals(rowData2)){
                            break;
                        }
                    }*/
                    flag = false;
                    for (Map<String, Integer> strMap : list) {
                        Integer firstRow = strMap.get("firstRow");//需要合并的第一个单元格的行数
                        Integer lastRow = strMap.get("lastRow");//需要合并的最后一个单元格的行数
                        Integer firstCol = strMap.get("firstCol");//需要合并的第一个单元格的列数
                        Integer lastCol = strMap.get("lastCol");//需要合并的最后一个单元格的列数
                        if((i>=firstRow&&i<=lastRow)&&(i2>=firstCol&&i2<=lastCol)){
                            flag = true;
                            break;
                        }
                    }
                    if(flag){
                        break;
                    }
                    if (rowData.equals(rowData2)){
                        ++rownum;
                    }else {
                        break;
                    }
                }
                int columnNum = 0;
                for (int i2 = i-numflag+1; i2 < headers.length; i2++) {
                    String[] columnData = headers[i2];
                    int colNum = 0;
                    boolean breakFlag = false;
                    for (int i3 = i1; i3 < columnData.length; i3++) {
                        String columnData2 = columnData[i3];
                        if (columnData2.equals(rowData)){
                            ++colNum;
                            breakFlag = true;
                        }else {
                            break;
                        }
                    }
                    if (rownum==colNum && breakFlag){
                        ++columnNum;
                    }else {
                        break;
                    }
                }
                cell = (SXSSFCell) row.createCell(i1);
                cell.setCellValue(rowData);
                cell.setCellStyle(head_Style);
                setColumnWidth(sheet, cell);
                --rownum;
                if (rownum!=0 || columnNum!=0){
                    int firstRow = cell.getRowIndex();//需要合并的第一个单元格的行数
                    int lastRow = firstRow+columnNum;//需要合并的最后一个单元格的行数
                    int firstCol = cell.getColumnIndex();//需要合并的第一个单元格的列数
                    int lastCol = firstCol+rownum;//需要合并的最后一个单元格的列数
                    Map<String,Integer> map = new HashMap();
                    map.put("firstRow",firstRow);
                    map.put("lastRow",lastRow);
                    map.put("firstCol",firstCol);
                    map.put("lastCol",lastCol);
                    list.add(map);
                    if (firstRow < lastRow || firstCol < lastCol) {
                        CellRangeAddress cellAddresses = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
                        sheet.addMergedRegion(cellAddresses);
                        // 使用RegionUtil类为合并后的单元格添加边框
                        RegionUtil.setBorderBottom(BorderStyle.THIN, cellAddresses, sheet); // 下边框
                        RegionUtil.setBorderLeft(BorderStyle.THIN, cellAddresses, sheet); // 左边框
                        RegionUtil.setBorderRight(BorderStyle.THIN, cellAddresses, sheet); // 有边框
                        RegionUtil.setBorderTop(BorderStyle.THIN, cellAddresses, sheet); // 上边框
                    }
                }
                //添加批注
                String rowcom = rowcommentsList[i1];
                if (comments!=null){
                    if (!"".equals(rowcom) && rowcom!=null){
                        ClientAnchor anchor = new XSSFClientAnchor();
                        anchor.setDx1(0);
                        anchor.setDx2(0);
                        anchor.setDy1(0);
                        anchor.setDy2(0);
                        anchor.setCol1(cell.getColumnIndex());
                        anchor.setRow1(cell.getRowIndex());
                        anchor.setCol2(cell.getColumnIndex()+5);
                        anchor.setRow2(cell.getRowIndex()+6);
                        SXSSFDrawing drawing= sheet.createDrawingPatriarch();//XSSFClientAnchor
                        Comment comment = drawing.createCellComment(anchor);
                        comment.setString(new XSSFRichTextString(rowcom));
                        if (sheet != null && sheet.getRow(0) != null && sheet.getRow(0).getCell(i) != null && comment != null) {
                            sheet.getRow(0).getCell(i).setCellComment(comment);
                        }
                    }
                }
                i1+=rownum;
            }
        }
    }
    /**
     * 创建表头
     * @param sheetName  工作薄名
     * @param headers 列名
     * @param comments 批注
     */
    private  void createSheet(String sheetName,String headers[],String comments[])  {
        sheet = (SXSSFSheet) workbook.createSheet(sheetName);
        row = (SXSSFRow) sheet.createRow(0);
        for (int i = 0; i < headers.length; i++) {
            cell = (SXSSFCell) row.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(head_Style);
            setColumnWidth(sheet, cell);
            //添加批注
            if (comments!=null){
                if (!"".equals(comments[i])&&comments[i]!=null){
                    ClientAnchor anchor = new XSSFClientAnchor();
                    anchor.setDx1(0);
                    anchor.setDx2(0);
                    anchor.setDy1(0);
                    anchor.setDy2(0);
                    anchor.setCol1(cell.getColumnIndex());
                    anchor.setRow1(cell.getRowIndex());
                    anchor.setCol2(cell.getColumnIndex()+5);
                    anchor.setRow2(cell.getRowIndex()+6);
                    SXSSFDrawing drawing= sheet.createDrawingPatriarch();//XSSFClientAnchor
                    Comment comment = drawing.createCellComment(anchor);
                    comment.setString(new XSSFRichTextString(comments[i]));
                    if (sheet != null && sheet.getRow(0) != null && sheet.getRow(0).getCell(i) != null && comment != null) {
                        sheet.getRow(0).getCell(i).setCellComment(comment);
                    }
                }
            }
        }
        currentRow=1;
    }

    /**
     * 设置自适应列宽
     *
     * @param sheet
     * @param cell
     */
    public static void setColumnWidth(SXSSFSheet sheet, Cell cell) {
        sheet.trackAllColumnsForAutoSizing();
        sheet.autoSizeColumn(cell.getColumnIndex());
        int colWidth = sheet.getColumnWidth(cell.getColumnIndex()) / 256;
        int width = cell.toString().getBytes().length;
        if (width > colWidth) {
            if (width > 255) {
                sheet.setColumnWidth(cell.getColumnIndex(), 254 * 256);
            } else {
                sheet.setColumnWidth(cell.getColumnIndex(), width * 256);
            }
        }
    }
    /**
     * 导出excel
     * @param listRows
     * @throws ParseException
     */
    public synchronized void PoiWriteExcel_To2007(List<List<Map<String,Object>>> listRows,String[][] nameArr,String[][] dataType,OutputStream out) throws ParseException {
        for (int i = 0; i < listRows.size(); i++) {
            currentRow=1;
            SXSSFSheet sheet1 = workbook.getSheetAt(i);
            String[] mapKey = nameArr[i];
            List<Map<String, Object>> sheetData = listRows.get(i);
            if (sheetData!=null){
                for (int i1 = 0; i1 < sheetData.size(); i1++) {
                    row = (SXSSFRow) sheet1.createRow(currentRow);
                    Map<String, Object> rowMap = sheetData.get(i1);
                    for (int i2 = 0; i2 < mapKey.length; i2++) {
                        Object cellData = rowMap.get(mapKey[i2]);
                        cell = (SXSSFCell) row.createCell(i2);
                        String str = "";
                        if(cellData!=null){
                            str = cellData.toString();
                        }
                        String strArr = dataType[i][i2];
                        if(Constant.NUMBER.equals(dataType[i][i2])){
                            if (str==null||"".equals(str)){
                                str="0";
                            }
                            cell.setCellType(CellType.NUMERIC);
                            if (str.contains(".")){
                                cell.setCellValue(Math.round(Double.parseDouble(str)));
                            }else {
                                cell.setCellValue(Integer.parseInt(str));
                            }
                            cell.setCellStyle(Integer_style);

                        }else if(Constant.DOUBLE.equals(strArr)){
                            if (str==null||"".equals(str)){
                                str="0.00";
                            }
                            cell.setCellType(CellType.NUMERIC);
                            cell.setCellValue(Double.parseDouble(str));
                            cell.setCellStyle(double_style);
                        }else if(Constant.DOUBLE2.equals(strArr)){//此处代表的是金额保留2位小数并加千分位
                            if (str==null||"".equals(str)){
                                str="0.00";
                            }
                            cell.setCellValue(Double.parseDouble(ObjectUtli.isEmpty(str)? "0":str.replaceAll(",","")));
                            //设置样式
                            cell.setCellStyle(double2_style);
                        } else{
                            cell.setCellType(CellType.STRING);
                            if(!"".equals(str)){
                                cell.setCellValue(str);
                            }else {
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(string_style);
                        }
                    }
                    currentRow ++;
                }
            }
        }
        try {
            workbook.write(out);
            workbook.close();
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出excel
     * @param listRows
     * @throws ParseException
     */
    public synchronized void PoiWriteExcel_To2007(List<List<Map<String,Object>>> listRows,String[][] dataType,OutputStream out) throws ParseException {
        for (int i = 0; i < listRows.size(); i++) {
            currentRow=rownumList.get(i);
            SXSSFSheet sheet1 = workbook.getSheetAt(i);
            String[] mapKey = keyList[i];
            List<Map<String,Integer>> list = new ArrayList();
            List<Map<String, Object>> sheetData = listRows.get(i);
            if (sheetData!=null){
                for (int i1 = 0; i1 < sheetData.size(); i1++) {
                    row = (SXSSFRow) sheet1.createRow(currentRow);
                    Map<String, Object> rowMap = JSON.parseObject(JSONArray.toJSONString(sheetData.get(i1)), HashMap.class);
                    for (int i2 = 0; i2 < mapKey.length; i2++) {
                        Object cellData = rowMap.get(mapKey[i2]);
                        cell = (SXSSFCell) row.createCell(i2);
                        boolean flag = false;
                        for (Map<String, Integer> strMap : list) {
                            Integer firstRow = strMap.get("firstRow");//需要合并的第一个单元格的行数
                            Integer lastRow = strMap.get("lastRow");//需要合并的最后一个单元格的行数
                            Integer firstCol = strMap.get("firstCol");//需要合并的第一个单元格的列数
                            Integer lastCol = strMap.get("lastCol");//需要合并的最后一个单元格的列数
                            if((currentRow>=firstRow&&currentRow<=lastRow)&&(i2>=firstCol&&i2<=lastCol)){
                                flag = true;
                                break;
                            }
                        }
                        if(flag){
                            continue;
                        }
                        String str= "";
                        if(cellData!=null){
                            str = cellData.toString();
                            if(!"".equals(str)){
                                int rownum = 1;
                                for (int rowInde = i2+1; rowInde < rowMap.size(); rowInde++) {
                                    Object obj = rowMap.get(mapKey[rowInde]);
                                    String colData = obj==null?"":obj.toString();
                                    flag = false;
                                    for (Map<String, Integer> strMap : list) {
                                        Integer firstRow = strMap.get("firstRow");//需要合并的第一个单元格的行数
                                        Integer lastRow = strMap.get("lastRow");//需要合并的最后一个单元格的行数
                                        Integer firstCol = strMap.get("firstCol");//需要合并的第一个单元格的列数
                                        Integer lastCol = strMap.get("lastCol");//需要合并的最后一个单元格的列数
                                        if((currentRow>=firstRow&&currentRow<=lastRow)&&(rowInde>=firstCol&&rowInde<=lastCol)){
                                            flag = true;
                                            break;
                                        }
                                    }
                                    if(flag){
                                        break;
                                    }
                                    if (str.equals(colData)){
                                        ++rownum;
                                    }else {
                                        break;
                                    }
                                }
                                int columnNum = 0;
                                for (int rowInde = i1+1; rowInde < sheetData.size(); rowInde++) {
                                    Map<String, Object> columnData = sheetData.get(rowInde);
                                    int colNum = 0;
                                    boolean breakFlag = false;
                                    for (int i3 = i2; i3 < columnData.size(); i3++) {
                                        Object colObj = columnData.get(mapKey[i3]);
                                        String colstr = "";
                                        if (colObj!=null){
                                            colstr=colObj.toString();
                                        }
                                        if (str.equals(colstr)){
                                            ++colNum;
                                            breakFlag = true;
                                        }else {
                                            break;
                                        }
                                    }
                                    if (rownum==colNum && breakFlag){
                                        ++columnNum;
                                    }else {
                                        break;
                                    }
                                }
                                --rownum;
                                if (rownum!=0 || columnNum!=0){
                                    int firstRow = cell.getRowIndex();//需要合并的第一个单元格的行数
                                    int lastRow = firstRow+columnNum;//需要合并的最后一个单元格的行数
                                    int firstCol = cell.getColumnIndex();//需要合并的第一个单元格的列数
                                    int lastCol = firstCol+rownum;//需要合并的最后一个单元格的列数
                                    Map<String,Integer> map = new HashMap();
                                    map.put("firstRow",firstRow);
                                    map.put("lastRow",lastRow);
                                    map.put("firstCol",firstCol);
                                    map.put("lastCol",lastCol);
                                    list.add(map);
                                    if (firstRow < lastRow || firstCol < lastCol) {
                                        CellRangeAddress cellAddresses = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
                                        sheet.addMergedRegion(cellAddresses);
                                       /* sheet.getRow(firstRow).getCell(firstCol).setCellStyle(merge_Style);
                                        // 使用RegionUtil类为合并后的单元格添加边框
                                        RegionUtil.setBorderBottom(BorderStyle.THIN, cellAddresses, sheet); // 下边框
                                        RegionUtil.setBorderLeft(BorderStyle.THIN, cellAddresses, sheet); // 左边框
                                        RegionUtil.setBorderRight(BorderStyle.THIN, cellAddresses, sheet); // 有边框
                                        RegionUtil.setBorderTop(BorderStyle.THIN, cellAddresses, sheet); // 上边框*/
                                    }
                                }
                            }
                        }
                        String strArr = dataType[i][i2];
                        if(Constant.NUMBER.equals(dataType[i][i2])){
                            if (str==null||"".equals(str)){
                                str="0";
                            }
                            cell.setCellType(CellType.NUMERIC);
                            if (str.contains(".")){
                                cell.setCellValue(Math.round(Double.parseDouble(str)));
                            }else {
                                cell.setCellValue(Integer.parseInt(str));
                            }
                            cell.setCellStyle(Integer_style);
                        }else if(Constant.DOUBLE.equals(strArr)){
                            if (str==null||"".equals(str)){
                                str="0.00";
                            }
                            cell.setCellType(CellType.NUMERIC);
                            cell.setCellValue(Double.parseDouble(str));
                            cell.setCellStyle(double_style);
                        }else if(Constant.DOUBLE2.equals(strArr)){//此处代表的是金额保留2位小数并加千分位
                            cell.setCellValue(Double.parseDouble(ObjectUtli.isEmpty(str)? "0":str.replaceAll(",","")));
                            //设置样式
                            cell.setCellStyle(double2_style);
                        } else{
                            cell.setCellType(CellType.STRING);
                            if(!"".equals(str)){
                                cell.setCellValue(str);
                            }else {
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(string_style);
                        }
                    }
                    currentRow ++;
                }
            }
        }
        try {
            workbook.write(out);
            workbook.close();
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    /**
     * 导出excel
     * @param listRows
     * @throws ParseException
     */
    public synchronized void PoiWriteExcel2_To2007(List<List<Map<String,Object>>> listRows,String[][] dataType,OutputStream out) throws ParseException {
        for (int i = 0; i < listRows.size(); i++) {
            currentRow=rownumList.get(i);
            SXSSFSheet sheet1 = workbook.getSheetAt(i);
            String[] mapKey = keyList[i];
            List<Map<String,Integer>> list = new ArrayList();
            List<Map<String, Object>> sheetData = listRows.get(i);
            if (sheetData!=null){
                for (int i1 = 0; i1 < sheetData.size(); i1++) {
                    row = (SXSSFRow) sheet1.createRow(currentRow);
                    Map<String, Object> rowMap = JSON.parseObject(JSONArray.toJSONString(sheetData.get(i1)), HashMap.class);
                    for (int i2 = 0; i2 < mapKey.length; i2++) {
                        Object cellData = rowMap.get(mapKey[i2]);
                        cell = (SXSSFCell) row.createCell(i2);
                        String str= "";
                        if(cellData!=null){
                            str = cellData.toString();
                        }
                        String strArr = dataType[i][i2];
                        if(Constant.NUMBER.equals(dataType[i][i2])){
                            if (str==null||"".equals(str)){
                                str="0";
                            }
                            cell.setCellType(CellType.NUMERIC);
                            if (str.contains(".")){
                                cell.setCellValue(Math.round(Double.parseDouble(str)));
                            }else {
                                cell.setCellValue(Integer.parseInt(str));
                            }
                            cell.setCellStyle(Integer_style);
                        }else if(Constant.DOUBLE.equals(strArr)){
                            if (str==null||"".equals(str)){
                                str="0.00";
                            }
                            cell.setCellType(CellType.NUMERIC);
                            cell.setCellValue(Double.parseDouble(str));
                            cell.setCellStyle(double_style);
                        }else if(Constant.DOUBLE2.equals(strArr)){//此处代表的是金额保留2位小数并加千分位
                            cell.setCellValue(Double.parseDouble(ObjectUtli.isEmpty(str)? "0":str.replaceAll(",","")));
                            //设置样式
                            cell.setCellStyle(double2_style);
                        } else{
                            cell.setCellType(CellType.STRING);
                            if(!"".equals(str)){
                                cell.setCellValue(str);
                            }else {
                                cell.setCellValue("");
                            }
                            cell.setCellStyle(string_style);
                        }
                    }
                    currentRow ++;
                }
            }
        }
        try {
            workbook.write(out);
            workbook.close();
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
    /**
     * 测试导出
     * @param args
     * @throws IOException
     * @throws ParseException
     */
    public static void main(String[] args) throws IOException, ParseException {
        long l = System.currentTimeMillis();
        File file = new File("C:\\Users\\12433\\Desktop\\测试.xlsx");
        if (file.exists())
            file.delete();
        file.createNewFile();
        String[] jobBookName={"数据源"};
        String[][] dataType = {{"STRING","STRING","STRING","NUMBER","DOUBLE","DOUBLE","DOUBLE","STRING"}};
        String[][][] strArr={{{"学校信息", "学校信息",  "学生信息", "学生信息", "学生信息", "学生信息", "学生信息", "学生信息"},
                {"学校信息", "学校信息",  "学生个人信息", "学生个人信息", "学生成绩信息", "学生成绩信息", "学生成绩信息", "学生信息"},
                {"学校", "班级",  "姓名", "年龄", "语文成绩", "数学成绩", "英语成绩", "备注信息"}}};
        String[][] order={{"schoolName","className","name","age","yuwenscore","shuxuescore","englishscore","remark"}};
        String[][][] comments={{{ "首位必须是字母,可包含下划线、数字", "", "字段类型：字符串，数字，日期", "", "", "字段用途：维度，量度，其它", "有效标识：启用，停用", "有效标识：启用，停用"},
                { "首位必须是字母,可包含下划线、数字", "", "字段类型：字符串，数字，日期", "", "", "字段用途：维度，量度，其它", "有效标识：启用，停用", "有效标识：启用，停用"},
                { "首位必须是字母,可包含下划线、数字", "", "字段类型：字符串，数字，日期", "", "", "字段用途：维度，量度，其它", "有效标识：启用，停用", "有效标识：启用，停用"}}};
        Map<String,Object> condition = new LinkedHashMap<>();
        condition.put("学校名称","XXX实验小学");
        condition.put("班级名称","终极一班");
        ExportData exportData = new ExportData( strArr,condition,jobBookName,comments,order);
        List<List<Map<String,Object>>> data = new ArrayList<List<Map<String,Object>>>();
        ArrayList<Map<String,Object>> cellList = new ArrayList();
        Map<String,Object> map=new HashMap<>();
        map.put("schoolName","XXX实验小学");
        map.put("className","终极一班");
        map.put("name","张三");
        map.put("age","");
        map.put("yuwenscore","98.5");
        map.put("shuxuescore","77");
        map.put("englishscore","89.5");
        map.put("remark","11");
        cellList.add(map);
        Map<String,Object> map1=new HashMap<>();
        map1.put("schoolName","XXX实验小学");
        map1.put("className","终极一班");
        map1.put("name","李四");
        map1.put("age","11");
        map1.put("yuwenscore","89.5");
        map1.put("shuxuescore","89.5");
        map1.put("englishscore","89.5");
        map1.put("remark","");
        cellList.add(map1);
        Map<String,Object> map2=new HashMap<>();
        map2.put("schoolName","XXX实验小学");
        map2.put("className","终极一班");
        map2.put("name","黎明");
        map2.put("age","24");
        map2.put("yuwenscore","9.5");
        map2.put("shuxuescore","82.51");
        map2.put("englishscore","82.51");
        map2.put("remark","");
        cellList.add(map2);
        for (int i = 0; i <10 ; i++) {
            Map<String,Object> map3=new HashMap<>();
            map3.put("schoolName","XXX实验小学");
            map3.put("className","终极一班");
            map3.put("name","孙悟空"+i);
            map3.put("age",i);
            map3.put("yuwenscore",i);
            if(i%2==0){
                map3.put("shuxuescore","82.5");
            }else{
                map3.put("shuxuescore","82.5"+i);
            }
            map3.put("englishscore","99.5"+i);
            map3.put("remark",i);
            cellList.add(map3);
        }
        data.add(cellList);
        OutputStream out = new FileOutputStream(file);
        exportData.PoiWriteExcel_To2007(data,dataType,out);
        long l1 = System.currentTimeMillis();
        out.close();

    }
}
